﻿using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Runtime.Remoting.Messaging;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Odbc;
using System.Collections;
//using System.Data.OracleClient;

namespace _180FrameWork.DataBaseHelper
{
    public class DbHelper : IDbHelper
    {
        protected Database _db;

        protected DbTransaction _dbTransaction;

        protected DbConnection _dbConnection;

        public DbHelper()
        {
            //_db = DatabaseFactory.CreateDatabase();


        }

        public DbHelper(string DBName)
        {
            if (DBName.Trim() == "")
                _db = DatabaseFactory.CreateDatabase();
            else
                _db = DatabaseFactory.CreateDatabase(DBName.Trim());
        }

        //public DbHelper(string DBProviderName, string DBConnectionString)
        //{

        //    switch (DBProviderName.ToLower())
        //    {
        //        case "sql":
        //            _db = new SqlDatabase(DBConnectionString);
        //            break;
        //        //case "mysql":
        //        //    _db = new GenericDatabase(DBConnectionString, MySql.Data.MySqlClient.MySqlClientFactory.Instance);
        //        //    break;
        //        //case "sqlite":
        //        //    _db = new GenericDatabase(DBConnectionString, System.Data.SQLite.SQLiteFactory.Instance);
        //        //    break;
        //        case "oracle":
        //            //_db = new GenericDatabase(DBConnectionString, System.Data.OracleClient.OracleClientFactory.Instance);
                    //_db = new Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase(DBConnectionString);
        //            break;
        //        default:
        //            _db = new GenericDatabase(DBConnectionString, OdbcFactory.Instance);
        //            break;
        //    }
        //}
        /// <summary>
        /// 根据sql语句封装dbcommond
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paramValues"></param>
        /// <returns></returns>
        private DbCommand GetDbCommandSql(string strSql, IDictionary<string, object> paramValues)
        {
            strSql = strSql.Trim();
            DbCommand command = _db.GetSqlStringCommand(strSql);            
            if (paramValues != null)
            {
                foreach (string strPara in paramValues.Keys)
                {
                    DbParameter para = command.CreateParameter();
                    para.ParameterName = strPara;
                    if (paramValues[strPara] != null)
                        para.Value = paramValues[strPara];
                    else
                        para.Value = DBNull.Value;
                    command.Parameters.Add(para);
                }
            }
            return command;
        }
        /// <summary>
        /// 根据存储过程封装dbcommond
        /// </summary>
        /// <param name="strSpName"></param>
        /// <param name="paramValues"></param>
        /// <returns></returns>
        private DbCommand GetDbCommandSp(string strSpName, object[] paramValues)
        {
            strSpName = strSpName.Trim();
            DbCommand command = _db.GetStoredProcCommand(strSpName);
            _db.AssignParameters(command, paramValues);
            return command;
        }

        #region IMyDbHelper 成员

        #region SQL
        /// <summary>
        /// 执行sql返回实体列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public IList<T> ExecuteSql<T>(string strSql) where T : new()
        {
            var da = _db.CreateSqlStringAccessor<T>(strSql);
            var e = da.Execute();
            IList<T> list = new List<T>();
            foreach (var item in e)
            {
                list.Add(item);
            }
            return list;
        }        

        /// <summary>
        /// 执行sql返回实体列表（带参数）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public IList<T> ExecuteSql<T>(string strSql, IDictionary<string, object> paramValues) where T : new()
        {
            IParameterMapper p = new MyParameterMapper(paramValues);
            var da = _db.CreateSqlStringAccessor<T>(strSql, p);
            var e = da.Execute();
            IList<T> list = new List<T>();
            foreach (var item in e)
            {
                list.Add(item);
            }
            return list;
        }
        
        /// <summary>
        /// 执行SQL语句，返回数据集
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataSet ExecuteSql(string strSql)
        {
            IDictionary<string, object> paramValues = new Dictionary<string, object>();
            DbCommand command = this.GetDbCommandSql(strSql, paramValues);
            if (_dbTransaction != null)
            {
                DataSet ds = _db.ExecuteDataSet(command, _dbTransaction);
                return ds;
            }
            else
            {
                DataSet ds = _db.ExecuteDataSet(command);
                return ds;
            }
        }
        /// <summary>
        /// 执行SQL语句，返回数据集（带参数）
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paramValues"></param>
        /// <returns></returns>
        public DataSet ExecuteSql(string strSql, IDictionary<string, object> paramValues)
        {
            DbCommand command = this.GetDbCommandSql(strSql, paramValues);
            if (_dbTransaction != null)
            {
                DataSet ds = _db.ExecuteDataSet(command, _dbTransaction);
                return ds;
            }
            else
            {
                DataSet ds = _db.ExecuteDataSet(command);
                return ds;
            }
        }
        /// <summary>
        /// 执行SQL语句，返回受影响的行数
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int ExecuteNoQuerySql(string strSql)
        {
            IDictionary<string, object> paramValues = new Dictionary<string, object>();
            DbCommand command = GetDbCommandSql(strSql, paramValues);
            if (_dbTransaction != null)
            {
                return _db.ExecuteNonQuery(command, _dbTransaction);
            }
            else
            {
                return _db.ExecuteNonQuery(command);
            }
        }
        /// <summary>
        /// 执行SQL语句，返回受影响的行数（带参数）
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paramValues"></param>
        /// <returns></returns>
        public int ExecuteNoQuerySql(string strSql, IDictionary<string, object> paramValues)
        {
            DbCommand command = GetDbCommandSql(strSql, paramValues);
            if (_dbTransaction != null)
            {
                return _db.ExecuteNonQuery(command, _dbTransaction);
            }
            else
            {
                return _db.ExecuteNonQuery(command);
            }
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool Exists(string strSql)
        {
            IDictionary<string, object> paramValues = new Dictionary<string, object>();
            DbCommand command = this.GetDbCommandSql(strSql, paramValues);

            DataSet ds = _db.ExecuteDataSet(command);
            if (ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        ///  是否存在（带参数）
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paramValues"></param>
        /// <returns></returns>
        public bool Exists(string strSql, IDictionary<string, object> paramValues)
        {
            DbCommand command = this.GetDbCommandSql(strSql, paramValues);

            DataSet ds = _db.ExecuteDataSet(command);
            if (ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 存储过程
        /// <summary>
        /// 执行sql返回实体列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public IList<T> ExecuteSp<T>(string strSql) where T : new()
        {
            var da = _db.CreateSprocAccessor<T>(strSql);
            var e = da.Execute();
            IList<T> list = new List<T>();
            foreach (var item in e)
            {
                list.Add(item);
            }
            return list;
        }

        /// <summary>
        /// 执行sql返回实体列表（带参数）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public IList<T> ExecuteSp<T>(string strSql, IDictionary<string, object> paramValues) where T : new()
        {
            IParameterMapper p = new MyParameterMapper(paramValues);
            var da = _db.CreateSprocAccessor<T>(strSql, p);
            var e = da.Execute();
            IList<T> list = new List<T>();
            foreach (var item in e)
            {
                list.Add(item);
            }
            return list;
        }
        
        /// <summary>
        /// 执行存储过程，返回数据集
        /// </summary>
        /// <param name="strSpName"></param>
        /// <param name="paramValues"></param>
        /// <returns></returns>
        public DataSet ExecuteSp(string strSpName, object[] paramValues)
        {
            DbCommand command = GetDbCommandSp(strSpName, paramValues);
            if (_dbTransaction != null)
            {
                DataSet ds = _db.ExecuteDataSet(command, _dbTransaction);
                return ds;
            }
            else
            {
                DataSet ds = _db.ExecuteDataSet(command);
                return ds;
            }            
        }
        /// <summary>
        /// 执行存储过程，返回受影响的行数
        /// </summary>
        /// <param name="strSpName"></param>
        /// <param name="paramValues"></param>
        /// <returns></returns>
        public int ExecuteNoQuerySp(string strSpName, object[] paramValues)
        {
            DbCommand command = GetDbCommandSp(strSpName, paramValues);
            if (_dbTransaction != null)
            {
                return _db.ExecuteNonQuery(command, _dbTransaction);
            }
            else
            {
                return _db.ExecuteNonQuery(command);
            }
        }

        public object[] ExecuteParamSp(string strSpName, object[] paramValues, string[] returnParamNames)
        {
            DbCommand command = GetDbCommandSp(strSpName, paramValues);
            if (_dbTransaction != null)
            {
                _db.ExecuteNonQuery(command, _dbTransaction);
            }
            else
            {
                _db.ExecuteNonQuery(command);
            }
            List<object> objlist = new List<object>();
            foreach (string pn in returnParamNames)
            {
                if (command.Parameters.Contains(pn))
                    //objlist.Add(command.Parameters[pn].Value);
                    objlist.Add(_db.GetParameterValue(command, pn));
            }
            return objlist.ToArray();
        }
        #endregion

        #region 事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTran()
        {
            if (_dbTransaction == null)
            {
                _dbConnection = _db.CreateConnection();
                _dbConnection.Open();
                _dbTransaction = _dbConnection.BeginTransaction();
            }
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            if (_dbTransaction != null)
            {
                _dbTransaction.Commit();
                _dbConnection.Close();
            }
            _dbTransaction = null;
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (_dbTransaction != null)
            {
                _dbTransaction.Rollback();
                _dbConnection.Close();
            }
            _dbTransaction = null;
        }
        #endregion

        #region 命令
        /// <summary>
        /// 执行命令，返回受影响的行数
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public int ExecuteNoQueryCmd(DbCommand command)
        {
            if (_dbTransaction != null)
            {
                return _db.ExecuteNonQuery(command, _dbTransaction);
            }
            else
            {
                return _db.ExecuteNonQuery(command);
            }
        }

        #endregion

        #endregion

    }
}
